package com.study.operateDB;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.study.operateDB.Enum.ExcuteStatus;
import com.study.operateDB.interfa.SQLResult;
import com.study.operateDB.interfa.impl.BaseSQLResult;
import com.study.operateDB.sql.PreparedSQL;
import com.study.operateDB.util.ConnectionUtil;

public class SQLUtil {
	private SQLUtil() {
	}

	public static SQLResult getDatabaseMetaData(Connection connection)
			throws SQLException {
		DatabaseMetaData databaseMetaData = connection.getMetaData();
		ResultSet resultSet = databaseMetaData.getTables(null, "DB2INST1", "%",
				null);

		try {
			BaseSQLResult baseResult = new BaseSQLResult(new PreparedSQL("",
					new Object[] {}));
			baseResult.setColumnNames(getColumnNames(resultSet));
			baseResult.setRows(getResults(resultSet));

			return baseResult;
		} finally {
			ConnectionUtil.closeResource(null, resultSet);
		}
	}

	public static ExcuteResult execute(Connection connection,
			PreparedSQL preparedSQL) throws SQLException {
		PreparedStatement statement = getPrepareStatement(connection,
				preparedSQL.getSql(), preparedSQL.getParams());

		try {
			int resultCount = statement.executeUpdate();
			ExcuteStatus excuteStatus = ConnectionUtil.excute(connection);
			return new ExcuteResult(resultCount, excuteStatus);
		} finally {
			ConnectionUtil.closeResource(statement, null);
		}
	}

	public static <T> T single(Connection connection, PreparedSQL preparedSQL,
			Class<T> clazz) throws SQLException {
		PreparedStatement statement = getPrepareStatement(connection,
				preparedSQL.getSql(), preparedSQL.getParams());
		ResultSet resultSet = statement.executeQuery();

		try {
			while (resultSet.next()) {
				return (T) resultSet.getObject(1);
			}

			return null;
		} finally {
			ConnectionUtil.closeResource(statement, resultSet);
		}
	}

	//
	// public static <T extends Bean> T firstRow(Connection connection,
	// PreparedSQL preparedSQL, BeanPacker<T> beanPack)
	// throws SQLException {
	// PreparedStatement statement = getPrepareStatement(connection,
	// preparedSQL.getSql(), preparedSQL.getParams());
	// ResultSet resultSet = statement.executeQuery();
	//
	// try {
	// List<Object[]> resultList = getResults(resultSet);
	// if (resultList.isEmpty()) {
	// return null;
	// } else {
	// Object[] innerResult = resultList.iterator().next();
	// return beanPack.pack(innerResult);
	// }
	// } finally {
	// ConnectionUtil.closeResource(statement, resultSet);
	// }
	// }

	public static <T> List<T> certainColumn(Connection connection,
			PreparedSQL preparedSQL, Class<T> clazz) throws SQLException {
		PreparedStatement statement = getPrepareStatement(connection,
				preparedSQL.getSql(), preparedSQL.getParams());
		ResultSet resultSet = statement.executeQuery();

		try {
			List<T> list = new ArrayList<T>();
			while (resultSet.next()) {
				list.add((T) resultSet.getObject(1));
			}

			return list;
		} finally {
			ConnectionUtil.closeResource(statement, resultSet);
		}
	}

	public static SQLResult query(Connection connection, PreparedSQL preparedSQL)
			throws SQLException {
		// Logger.getLogger(SQLUtil.class).debug(preparedSQL.getSql());
		PreparedStatement statement = getPrepareStatement(connection,
				preparedSQL.getSql(), preparedSQL.getParams());
		ResultSet resultSet = statement.executeQuery();

		try {
			BaseSQLResult baseResult = new BaseSQLResult(preparedSQL);
			baseResult.setColumnNames(getColumnNames(resultSet));
			baseResult.setRows(getResults(resultSet));

			return baseResult;
		} finally {
			ConnectionUtil.closeResource(statement, resultSet);
		}
	}

	public static PreparedStatement getPrepareStatement(Connection connection,
			String sql, Object[] objects) throws SQLException {
		PreparedStatement statement = connection.prepareStatement(sql);
		return setParameters(statement, objects);
	}

	private static PreparedStatement setParameters(PreparedStatement statement,
			Object[] objects) throws SQLException {
		for (int index = 1; index < objects.length + 1; index++) {
			statement = setParameter(statement, index, objects[index - 1]);
		}

		return statement;
	}

	private static PreparedStatement setParameter(PreparedStatement statement,
			int paramIndex, Object object) throws SQLException {
		statement.setObject(paramIndex, object);
		return statement;
	}

	private static String[] getColumnNames(ResultSet resultSet)
			throws SQLException {
		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();

		String[] columnNames = new String[columnCount];
		for (int column = 0; column < columnCount; column++) {
			columnNames[column] = metaData.getColumnName(column + 1);
		}

		return columnNames;
	}

	private static List<Object[]> getResults(ResultSet resultSet)
			throws SQLException {
		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();

		List<Object[]> results = new ArrayList<Object[]>(100);
		while (resultSet.next()) {
			Object[] innerResult = new Object[columnCount];
			for (int column = 0; column < columnCount; column++) {
				innerResult[column] = resultSet.getObject(column + 1);
			}
			results.add(innerResult);
		}

		return results;
	}
}
